package com.miao.util.poi;

import com.miao.util.string.BankStringUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.awt.*;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;


public class POISSUtil {

    public static final int NONMERGE = 0;
    public static final int FIRSTMERGE = 2;
    public static final int INMERGE_BUT_NOFIRST = 1;
    private static Logger logger = Logger.getLogger(POISSUtil.class);

    /**
     * 返回一个SHEET的 对应坐标的CELL
     *
     * @param sheet
     * @param x     ROW NUM
     * @param y     COLUMN NUM
     * @return 返回HSSFCell对象
     */
    public static Cell getCell(Sheet sheet, int x, int y) {
        if ((sheet != null) && (x >= 0) && (y >= 0)) {
            Row row = sheet.getRow(x);
            if (row == null) {
                return null;
            } else {
                Cell cell = row.getCell(y);
                return cell;
            }
        } else
            return null;
    }

    /**
     * 返回一个CELL的 对应坐标(Point对象)
     *
     * @param cell
     * @return 返回Point对象
     */
    public static Point getCellPoint(Cell cell) {
        if (cell != null)
            return new Point(cell.getRowIndex(), cell.getColumnIndex());
        else
            return null;
    }

    /**
     * 返回一个CELL的 对应format(如"B3")
     *
     * @param cell
     * @return 返回String(如"B3")
     */
    public static String getCellFormatStr(Cell cell) {
        if (cell != null) {
            return AxisFormulaTrans.getCellPos(cell.getRowIndex(), cell.getColumnIndex());
        } else
            return null;
    }

    /**
     * 获取行的实际列数,从右向左数到有内容的格停止,返回实际格数,即不取右边的空格
     *
     * @param row
     * @return
     * @throws Exception
     */
    public static int getRowLength(Row row) throws Exception {
        int length = row.getLastCellNum();
        for (int i = length - 1; i >= 0; i--) {
            Cell cell = row.getCell(i);
            // 如果是公式格,认为此格非空,则返回到此格为止的行长.
            if (isCellFormula(cell)) {
                return length;
            }
            // 如果不是公式格,取其值,如果值为空,则继续左移,如果值不为空,,则返回到此格为止的行长.
            else {
                String cellValue = getCellValueByCellType2003(cell, "yyyyMMdd", "##", "0");
                if (StringUtils.isBlank(cellValue))
                    length--;
                else
                    return length;
            }

        }
        return 0;

    }

    /**
     * 建立基本的Workbook对象
     *
     * @return
     * @throws IOException FILENOFOUND异常抛出
     * @throws Exception   判空和文件类型检验的异常抛出
     */
    public static Workbook getWorkbook(String filePath) {
        if (!StringUtils.isBlank(filePath)) {
            try {
                FileInputStream fileIn = new FileInputStream(filePath);
                return WorkbookFactory.create(fileIn);
            } catch (FileNotFoundException e) {
                logger.info("打开EXCEL文件失败:" + e.getMessage());
                logger.info("建立新文件");
                return new XSSFWorkbook();
            } catch (IOException e) {
                logger.info("打开EXCEL文件失败:" + e.getMessage());
//				throw new Exception("打开EXCEL文件失败:"+e.getMessage());
            } catch (InvalidFormatException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
                return null;
            }
        } else {
            logger.info("打开EXCEL文件失败:路径为空");
//			throw new Exception("打开EXCEL文件失败:路径为空");
        }
        return null;
    }

    /**
     * 获取一个EXCEL文件对象里的所有SHEET的名字
     *
     * @param wb
     * @return
     */
    public static String[] getSheetNames(Workbook wb) {
        if (wb == null)
            return null;
        int sheetNum = wb.getNumberOfSheets();
        if (sheetNum == 0)
            return null;
        String[] sheetNames = new String[sheetNum];
        for (int i = 0; i < sheetNum; i++)
            sheetNames[i] = wb.getSheetName(i);
        return sheetNames;
    }

    /**
     * 返回一个SHEET的INDEX
     *
     * @param wb
     * @param sheetName
     * @return 参数判空失败的情况下, 返回-1
     * 不存在该SHEET,返回-1
     */
    public static int getSheetNum(Workbook wb, String sheetName) {
        if ((wb == null) || (StringUtils.isBlank(sheetName)))
            return -1;
        if (wb.getSheet(sheetName) == null)
            return -1;
        else
            return wb.getSheetIndex(sheetName);
    }

    /**
     * 返回一个SHEET的NAME
     *
     * @param wb
     * @return 返回sheet名, 判空失败返回null
     * 不存在该SHEET返回null
     */
    public static String getSheetName(Workbook wb, int sheetIndex) {
        if ((wb == null) || (sheetIndex < 0))
            return null;
        if (wb.getSheetAt(sheetIndex) == null)
            return null;
        else
            return wb.getSheetName(sheetIndex);
    }


    /**
     * 获取SHEET的最大列值 (即SHEET有多少列)
     *
     * @param wb
     * @param sheetName
     * @return 返回最大列值, 此值是BASE 1的,不是BASE 0的.
     */
    public static int getSheetColumnsNum(Workbook wb, String sheetName) {
        if ((wb == null) || (StringUtils.isBlank(sheetName)))
            return -1;
        Sheet sheet1 = wb.getSheet(sheetName);
        if (sheet1 == null)
            return -1;

        int maxColumns = 0;
        for (int rowNumOfSheet = 0; rowNumOfSheet <= sheet1.getLastRowNum(); rowNumOfSheet++) {
            if (null != sheet1.getRow(rowNumOfSheet)) {
                Row aRow = sheet1.getRow(rowNumOfSheet);
                if (aRow.getLastCellNum() > maxColumns)
                    maxColumns = aRow.getLastCellNum();
            }
        }

        return maxColumns + 1;
    }


    /**
     * 获取CELL值,不仅计算了FORMULA的值,还对浮点数和日期型数都进行了FORMAT
     * 日期型数的FORMAT作为参数输入,浮点数的FORMAT和浮点数的为0表示都取缺省值
     *
     * @param aCell
     * @param evaluator  FORMULA计算需要evaluator
     * @param dateFormat 日期型数FORMAT,如"yyyy年MM月"
     * @return
     */
    public static String getCellValue(Cell aCell, FormulaEvaluator evaluator,
                                      String dateFormat, String numFormat, String zeroFormat) {

        if (aCell == null)
            return null;

        StringBuilder sb = new StringBuilder();

        switch (aCell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                // POI 数字和日期不分,必须手工分开,手工转换成日期
                if (DateUtil.isCellDateFormatted(aCell)) {
                    sb.append(BankStringUtil.dateNumericDisp(aCell.getDateCellValue(), dateFormat));

                } else {
                    sb.append(BankStringUtil.doubleNumericDisp(aCell.getNumericCellValue(), numFormat, zeroFormat));
                }
                break;

            case Cell.CELL_TYPE_STRING:
                sb.append(aCell.getRichStringCellValue().getString());
                break;

            case Cell.CELL_TYPE_BOOLEAN:
                sb.append(aCell.getBooleanCellValue());
                break;

            case Cell.CELL_TYPE_FORMULA:

                CellValue cellValue = evaluator.evaluate(aCell);
                switch (cellValue.getCellType()) {
                    case Cell.CELL_TYPE_BOOLEAN:
                        sb.append(cellValue.getBooleanValue());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        sb.append(BankStringUtil.doubleNumericDisp(aCell.getNumericCellValue(), numFormat, zeroFormat));
                        break;
                    case Cell.CELL_TYPE_STRING:
                        sb.append(cellValue.getStringValue());
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        break;

                    // CELL_TYPE_FORMULA will never happen
                    case Cell.CELL_TYPE_FORMULA:
                        break;
                }// end of switch

                break;
            default:
                sb.append("");
        }// end of switch

        return sb.toString();
    }

    public static String getRowCellStringValue(Row row, int i) throws Exception {
        if ((row == null) || (row.getLastCellNum() <= i) || (i < 0) || row.getCell(i) == null)
            return "";
        Cell cell = row.getCell(i);
        String cellValue = getCellValueByCellType2003(cell, "yyyyMMdd", "##", "0");
        return cellValue;
    }


    /**
     * 根据类型取cell值
     *
     * @param cell
     * @return
     * @throws Exception
     */
    // 公式不计算, 直接返回null,  其他(default)也返回null
    public static String getCellValueByCellType2003(Cell cell, String dateFormat, String numFormat, String zeroFormat)
            throws Exception {

        if (cell == null)
            return null;

        String retString = null;

        switch (cell.getCellType()) {

            case Cell.CELL_TYPE_NUMERIC:
                // POI 数字和日期不分,必须手工分开,手工转换成日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    retString = BankStringUtil.dateNumericDisp(cell.getDateCellValue(), dateFormat);
                } else {
                    // 数字不分整数和浮点,double型,小数点会显示很大,必须手工将小数点设到2位
                    //根据输入的不同FORMAT类型,分为整数和二位小数的浮点数
                    retString = BankStringUtil.doubleNumericDisp(cell.getNumericCellValue(), numFormat, zeroFormat);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                retString = cell.getRichStringCellValue().toString();
                break;
            case Cell.CELL_TYPE_FORMULA:
                logger.info("尚未添加公式的设置");
                break;
            case Cell.CELL_TYPE_BLANK:
                retString = cell.getRichStringCellValue().toString();
        }

        return retString;
    }

    /**
     * 根据类型取cell值
     *
     * @param cell
     * @return
     * @throws Exception
     * @throws Exception
     */
    // 公式不计算, 但抛异常,  其他(default)也返回null
    public static String getCellValueByCellType2003Exception(Cell cell, String dateFormat, String numFormat, String zeroFormat)
            throws Exception, Exception {

        if (cell == null)
            return null;

        String retString = null;

        switch (cell.getCellType()) {

            case Cell.CELL_TYPE_NUMERIC:
                // POI 数字和日期不分,必须手工分开,手工转换成日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    retString = BankStringUtil.dateNumericDisp(cell.getDateCellValue(), dateFormat);
                } else {
                    // 数字不分整数和浮点,double型,小数点会显示很大,必须手工将小数点设到2位
                    //根据输入的不同FORMAT类型,分为整数和二位小数的浮点数
                    retString = BankStringUtil.doubleNumericDisp(cell.getNumericCellValue(), numFormat, zeroFormat);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                retString = cell.getRichStringCellValue().toString();
                break;
            case Cell.CELL_TYPE_FORMULA:
                logger.info("尚未添加公式的设置");
                throw new Exception("单元格[" + AxisFormulaTrans.getCellFormatStr(cell) + "]含有公式");
            case Cell.CELL_TYPE_BLANK:
                retString = cell.getRichStringCellValue().toString();
        }

        return retString;
    }

    /**
     * 获取CELL FORMULA计算式字串,如果CELL没有计算式,就返回其值(并且进行FORMAT)
     *
     * @param aCell
     * @param dateFormat 日期型FORMAT,如"yyyy年MM月"
     * @return
     */
    public static String getCellFormula(Cell aCell, String dateFormat) {

        StringBuilder sb = new StringBuilder();

        switch (aCell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                sb.append(aCell.getRichStringCellValue().getString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(aCell)) {
                    sb.append(BankStringUtil.dateNumericDisp(aCell.getDateCellValue(), dateFormat));

                } else {
                    sb.append(BankStringUtil.doubleNumericDisp(aCell.getNumericCellValue(), BankStringUtil.DOUBLEFORMAT, BankStringUtil.ZEROFORMAT));
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                sb.append(aCell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                sb.append(aCell.getCellFormula());
                break;
            default:
                sb.append("");
        }// end of switch

        return sb.toString();
    }


    /**
     * 获取CELL FORMULA
     *
     * @param aCell
     * @return true false
     */
    public static boolean isCellFormula(Cell aCell) {

        if (aCell == null) {
            return false;
        }
        if (aCell.getCellType() == Cell.CELL_TYPE_FORMULA)
            return true;
        else
            return false;
    }

    /**
     * 判断CELL是否是合并单元格
     *
     * @param sheet
     * @param cell
     * @return 在合并单元格中, 且是首格, 返回2
     * 在合并单元格中,但不是首格,返回1
     * 不在合并单元格中,返回0
     */
    public static int isMergedRegion(Sheet sheet, Cell cell) {

        //获得一个 sheet 中合并单元格的总数
        int sheetmergerCount = sheet.getNumMergedRegions();

        // 遍历SHEET中的每个合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);

            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            int firstC = ca.getFirstColumn();
            int lastC = ca.getLastColumn();
            int firstR = ca.getFirstRow();
            int lastR = ca.getLastRow();

            //判断参数单元格是否是在合并单元格中,
            //如果是, 则返回所在合并单元格的首单元格的值
            if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) {
                if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR) {
                    if ((cell.getRowIndex() == firstR) && (cell.getColumnIndex() == firstC))
                        return FIRSTMERGE; //在合并单元格中,且是首格
                    else
                        return INMERGE_BUT_NOFIRST;  //在合并单元格中,但不是首格
                } // end of if
            }  //end of if
        }// end of for

        return NONMERGE;   // 不在合并单元格中
    }

    /**
     * 判断参数CELL是否是SHEET中合并单元格,如果是,返回该合并单元格的首格
     *
     * @param sheet
     * @param cell
     * @return 返回合并单元格的首格, 或NULL(参数不是合并单元格)
     */
    public static CellRangeAddress getMergedRegion(Sheet sheet, Cell cell) {

        //获得一个 sheet 中合并单元格的总数
        int sheetmergerCount = sheet.getNumMergedRegions();

        // 遍历SHEET中的每个合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            //获得合并单元格的起始行, 结束行, 起始列, 结束列
            int firstC = ca.getFirstColumn();
            int lastC = ca.getLastColumn();
            int firstR = ca.getFirstRow();
            int lastR = ca.getLastRow();

            //判断参数单元格是否是在合并单元格中,
            //如果是, 则返回所在合并单元格的首单元格的值
            if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) {
                if (cell.getRowIndex() <= lastR && cell.getRowIndex() >= firstR) {
                    return ca;
                } // end of if
            }  //end of if
        }// end of for

        return null;
    }

    public final static void main(String[] args) {
        //String filePath= "d:\\7.20.xls";
        String filePath = "d:\\7.20.xlsx";
        Workbook wb = getWorkbook(filePath);
        Sheet sheet = wb.getSheetAt(0);
        System.out.println(sheet.getSheetName());

        String[] sheetNames = getSheetNames(wb);
        for (String temp : sheetNames)
            System.out.println(temp);

    }
}
